﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Text.RegularExpressions;

namespace OISTools
{
    public partial class ConvertProcToPGSql : Form
    {
        public ConvertProcToPGSql()
        {
            InitializeComponent();
        }

        private void btnReplace_Click(object sender, EventArgs e)
        {
            string RawText = richTextBox1.Text;
            foreach (var r in list1)
            {
                //在更新 set 時,需要指定行開始,不然會錯誤地更新到　update 
                // update table1 set a= b
                Regex reg = new Regex(r.Key, RegexOptions.Multiline| RegexOptions.IgnoreCase);

                RawText = reg.Replace(RawText, r.Value);
            }

            foreach (var r in list2)
            {
                Regex reg = new Regex(r.Key, RegexOptions.Multiline | RegexOptions.IgnoreCase);
                reg.Replace(RawText, r.Value);
            }


            RawText += @" 

end
$BODY$
  LANGUAGE 'plpgsql' VOLATILE";

            richTextBox2.Text = RawText;
        }

        private void suggest(string RawText)
        {
            if (RawText.ToLower().IndexOf(" top ") > 0)
            {
                listBox1.Items.Add("存在　ＴＯＰ　请使用 limit 代替");
            }
        }


        Dictionary<string, string> list1 = new Dictionary<string, string>();

        Dictionary<string, MatchEvaluator> list2 = new Dictionary<string, MatchEvaluator>();
        private void ConvertProcToPGSql_Load(object sender, EventArgs e)
        {
            list1.Add(@"ALTER\s*function\s*\[dbo\].\[([^\]]*)]", @"CREATE OR REPLACE FUNCTION $1");
            list1.Add(@"ALTER\s*procedure\s*\[dbo\].\[([^\]]*)]", @"CREATE OR REPLACE FUNCTION $1");
            list1.Add("@", "i_");
            list1.Add(@"nvarchar", @"character varying");
            list1.Add(@"varchar", @"character varying");

            list1.Add(@"GO\n", @";");

            list1.Add(@" ntext ", " text ");

            list1.Add(@"(as)\n|(AS)\n", @"AS
$BODY$
");

            list1.Add(@"^\s*set\s{1,}(.*)=(.*)", @"$1:=$2;");

            list1.Add(@"isnull", @"coalesce");

            list1.Add(@"\+", @" || ");

            list1.Add(@"^\s*declare\s*(\w*)\s*cursor\s*for", "for $1 in");

            list1.Add(@"^\s*(declare\s*.*)$", "$1;");

            list1.Add(@"create\s*table\s*#", "create local temporary table ");

            list1.Add(@"#", "");

            list1.Add(@"char\(39\)", "chr(39)");

            list1.Add("datetime", "timestamp");

            list1.Add(@"\[(\w*)\]","$1");


            list2.Add(@"^\s*(select.*=.*)from", replaceWithMethod_1);
        }



        private string replaceWithMethod_1(Match mm)
        {
            //表達式里面可能包含了逗號，所以此處無法以逗號作為分隔符。
            //t_inival=isnull(sum(isnull(dayval,0)),0),a=2
            //[\w]*=.*,
            //有空再試了
            string input= mm.Groups[1].Value;
            string[] items= input.Split(",".ToCharArray());
            string keys="",values="";
            foreach (string item in items)
            {
                string[] subItem= item.Split("=".ToCharArray());
                keys += "," + subItem[0];
                values += "," + subItem[1];
            }

            keys = keys.Substring(1);
            values = values.Substring(1);

            return string.Format("select {0} into {1} from ", values, keys);
        }
    }
}
